﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using System.Data;
using Spring.Data.Generic;
using Spring.Data.Common;
using Spring.Data.Objects.Generic;

namespace MySearch
{
    public enum FieldAndMemberBaseDAOType { Insert, Select, Delete };

    public struct FieldAndMemberBaseSql
    {
        public Type ElementType;
        public Type FiledOrMemberType;
        public FieldAndMemberBaseDAOType DAOType;
        public string Sql;

        public FieldAndMemberBaseSql(Type elementType, Type childType, FieldAndMemberBaseDAOType daoType, string sql)
        {
            ElementType = elementType;
            FiledOrMemberType = childType;
            DAOType = daoType;
            Sql = sql;
        }
    }

    public static class FieldAndMemberDAOBase
    {
        private static AdoTemplate template = AppConfigurer.AdoTemplate;

        private static IList<FieldAndMemberBaseSql> sqls;

        static FieldAndMemberDAOBase()
        {
            sqls = new List<FieldAndMemberBaseSql>();

            sqls.Add(new FieldAndMemberBaseSql(typeof(Chargeable), typeof(CommonField), FieldAndMemberBaseDAOType.Insert, "insert into Chargeable_Field values(@code, @name, @value, @type)"));
            sqls.Add(new FieldAndMemberBaseSql(typeof(Chargeable), typeof(CommonField), FieldAndMemberBaseDAOType.Select, "select Field_Name, Field_Value, Data_Type from Chargeable_Field where Chargeable_Code=@code"));
            sqls.Add(new FieldAndMemberBaseSql(typeof(Chargeable), typeof(CommonMember), FieldAndMemberBaseDAOType.Insert, "insert into Chargeable_Member values(@code, @staffno, @role, @is_Sync, @status)"));
            sqls.Add(new FieldAndMemberBaseSql(typeof(Chargeable), typeof(CommonMember), FieldAndMemberBaseDAOType.Select, "select Staff_No, Staff_Role, Is_Sync, [Status] from Chargeable_Member where Chargeable_Code=@code"));

            sqls.Add(new FieldAndMemberBaseSql(typeof(Profile), typeof(CommonField), FieldAndMemberBaseDAOType.Insert, "insert into Profile_Field values(@code, @name, @value, @type)"));
            sqls.Add(new FieldAndMemberBaseSql(typeof(Profile), typeof(CommonField), FieldAndMemberBaseDAOType.Select, "select Field_Name, Field_Value, Data_Type from Profile_Field where Profile_Code=@code"));
            sqls.Add(new FieldAndMemberBaseSql(typeof(Profile), typeof(CommonMember), FieldAndMemberBaseDAOType.Insert, "insert into Profile_Member values(@code, @staffno, @role, @is_Sync, @status)"));
            sqls.Add(new FieldAndMemberBaseSql(typeof(Profile), typeof(CommonMember), FieldAndMemberBaseDAOType.Select, "select Staff_No, Staff_Role, Is_Sync, [Status] from Profile_Member where Profile_Code=@code"));
        }

        private static string GetSql(FieldAndMemberBase element, Type filedOrMemberType, FieldAndMemberBaseDAOType daoType)
        {
            return sqls.SingleOrDefault(e => e.ElementType == element.GetType() && e.FiledOrMemberType == filedOrMemberType && e.DAOType == daoType).Sql;
        }

        public static TElement Get<TElement>(string code, string sql, Func<IDataReader, int, TElement> precidate)
            where TElement : FieldAndMemberBase
        {
            if (string.IsNullOrWhiteSpace(code) || string.IsNullOrWhiteSpace(sql)
                || precidate == null)
                return null;

            IDbParameters p = template.CreateDbParameters();
            p.Add("code", DbType.String).Value = code;

            IList<TElement> lst = template.QueryWithRowMapperDelegate(CommandType.Text, sql,
                new RowMapperDelegate<TElement>(precidate), p);

            TElement element = lst.Count > 0 ? lst[0] : null;

            if (element == null)
                return null;

            // populate fields
            string sqlField = GetSql(element, typeof(CommonField), FieldAndMemberBaseDAOType.Select);
            template.QueryWithRowCallbackDelegate(CommandType.Text, sqlField, delegate(IDataReader dr)
            {
                element.AddField(dr.GetString(0), (CommonDataType)Enum.Parse(typeof(CommonDataType), dr.GetString(2)), dr.GetString(1));
            }, p);

            // populate members
            string sqlMember = GetSql(element, typeof(CommonMember), FieldAndMemberBaseDAOType.Select);
            template.QueryWithRowCallbackDelegate(CommandType.Text, sqlMember, delegate(IDataReader dr)
            {
                element.AddMember(new CommonMember(
                    dr.GetString(0),
                    (CommonMemberRole)Enum.Parse(typeof(CommonMemberRole), dr.GetString(1)),
                    dr.GetString(2) == "Y" ? true : false,
                    (CommonStatus)Enum.Parse(typeof(CommonStatus), dr.GetString(3))
                ));
            }, p);

            return element;
        }

        private static IDbParameters CreateFiledParameters(string code, CommonField field)
        {
            IDbParameters paras = template.CreateDbParameters();
            paras.Add("code", DbType.String).Value = code;
            paras.Add("name", DbType.String).Value = field.Name;
            paras.Add("value", DbType.String).Value = field.Value;
            paras.Add("type", DbType.String).Value = field.DataType.ToString();

            return paras;
        }

        private static IDbParameters CreateMemberParameters(string code, CommonMember member)
        {
            IDbParameters paras = template.CreateDbParameters();
            paras.Add("code", DbType.String).Value = code;
            paras.Add("staffno", DbType.String).Value = member.StaffNo;
            paras.Add("role", DbType.String).Value = member.Role.ToString();
            paras.Add("is_Sync", DbType.String).Value = member.IsSync ? "Y" : "N";
            paras.Add("status", DbType.String).Value = member.Status.ToString();
            return paras;
        }

        public static void InsertFieldAndMember(string code, FieldAndMemberBase element)
        {
            InsertField(code, element);
            InsertMember(code, element);
        }

        public static void InsertField(string code, FieldAndMemberBase element)
        {
            // add field
            string sqlInsert = GetSql(element, typeof(CommonField), FieldAndMemberBaseDAOType.Insert);
            foreach (var field in element.Fields)
                template.ExecuteNonQuery(CommandType.Text, sqlInsert, CreateFiledParameters(code, field.Value));
        }

        public static void InsertMember(string code, FieldAndMemberBase element)
        {
            // add field
            string sqlInsert = GetSql(element, typeof(CommonMember), FieldAndMemberBaseDAOType.Insert);
            foreach (var member in element.Members)
                template.ExecuteNonQuery(CommandType.Text, sqlInsert, CreateMemberParameters(code, member.Value));
        }
    }

    internal static class ChargeableDAO
    {
        private static AdoTemplate template = AppConfigurer.AdoTemplate;

        internal static Chargeable GetChargeable(string chargeCode)
        {
            string strSql = "select chargeable_code, chargeable_type, profile_code, [Description], "
                + "Inherit_Profile_Member, [Status], Source_Type  FROM Chargeable "
                + "WHERE Chargeable_Code = @code  ";

            return FieldAndMemberDAOBase.Get<Chargeable>(chargeCode, strSql, MappingRow);
        }

        internal static Chargeable GetChargeable(string chargeCode, string profileCode)
        {
            string strSql = "select chargeable_code, chargeable_type, profile_code, [Description], "
                + "Inherit_Profile_Member, [Status], Source_Type  FROM Chargeable "
                + "WHERE Chargeable_Code = @chargeableCode and profile_code = @profileCode ";

            IDbParameters p = template.CreateDbParameters();
            p.Add("chargeableCode", DbType.String).Value = chargeCode;
            p.Add("profileCode", DbType.String).Value = profileCode;

            IList<Chargeable> lst = template.QueryWithRowMapperDelegate(CommandType.Text, strSql,
                new RowMapperDelegate<Chargeable>(MappingRow), p);

            if (lst == null || lst.Count() < 1)
                return null;

            return lst[0];
        }

        internal static IList<string> ListMyChargeableCode(string staffNo)
        {
            string strSql = " select chargeable_code from Chargeable_Member where Staff_No=@staffNo ";

            IDbParameters p1 = template.CreateDbParameters();
            p1.Add("staffNo", DbType.String).Value = staffNo;

            IList<string> lstRet = new List<string>();

            template.QueryWithRowCallbackDelegate(CommandType.Text, strSql, delegate(IDataReader dr)
            {
                lstRet.Add(dr.GetString(0));

            }, p1);

            return lstRet;

        }

        internal static IList<string> ListProfileChargeableCode(string profileCode)
        {
            string strSql = " select chargeable_code from Chargeable where profile_code=@profileCode ";

            IDbParameters p1 = template.CreateDbParameters();
            p1.Add("profileCode", DbType.String).Value = profileCode;

            IList<string> lstRet = new List<string>();

            template.QueryWithRowCallbackDelegate(CommandType.Text, strSql, delegate(IDataReader dr)
            {
                lstRet.Add(dr.GetString(0));

            }, p1);

            return lstRet;

        }

        internal static IList<CommonMember> ListChargeableMember(string chargeCode)
        {
            IList<CommonMember> lstRet = new List<CommonMember>();

            string strSql = "select chargeable_code, staff_no, staff_role, is_sync, status from chargeable_member "
                + "WHERE Chargeable_Code = @chargeableCode ";

            IDbParameters p = template.CreateDbParameters();
            p.Add("chargeableCode", DbType.String).Value = chargeCode;

            template.QueryWithRowCallbackDelegate(CommandType.Text, strSql, delegate(IDataReader dr)
            {
                string staffNo = dr.GetString(1);
                CommonMemberRole staffRole = (CommonMemberRole)Enum.Parse(typeof(CommonMemberRole), dr.GetString(2));
                bool isSync = dr.GetString(3) == "Y" ? true : false;
                CommonStatus status = (CommonStatus)Enum.Parse(typeof(CommonStatus), dr.GetString(4));

                CommonMember cm = new CommonMember(staffNo, staffRole, isSync, status);

                lstRet.Add(cm);

            }, p);

            return lstRet;
        }

        internal static IList<CommonField> ListChargeableField(string chargeCode)
        {
            IList<CommonField> lstRet = new List<CommonField>();

            string strSql = "select chargeable_code, Field_Name, Field_Value, Data_Type from chargeable_field "
                + "WHERE Chargeable_Code = @chargeableCode ";

            IDbParameters p = template.CreateDbParameters();
            p.Add("chargeableCode", DbType.String).Value = chargeCode;

            template.QueryWithRowCallbackDelegate(CommandType.Text, strSql, delegate(IDataReader dr)
            {
                string name = dr.GetString(1);
                string value = dr.GetString(2);
                CommonDataType dataType = (CommonDataType)Enum.Parse(typeof(CommonDataType), dr.GetString(3));

                CommonField cf = new CommonField(name, dataType, value);
                lstRet.Add(cf);

            }, p);

            return lstRet;
        }

        internal static bool CheckChargeableCode(string chargeCode, string profileCode)
        {
            string sqlSel = " select Chargeable_Code from Chargeable  where Chargeable_Code=@chargeableCode and Profile_Code=@profileCode ";

            IDbParameters p = template.CreateDbParameters();
            p.Add("chargeableCode", DbType.String).Value = chargeCode;
            p.Add("profileCode", DbType.String).Value = profileCode;

            string strCode = null;
            template.QueryWithRowCallbackDelegate(CommandType.Text, sqlSel, delegate(IDataReader dr)
            {
                strCode = dr.GetString(0);

            }, p);

            return !string.IsNullOrEmpty(strCode);
        }

        internal static void CreateChargeable(Chargeable obj)
        {
            string strIns = " insert into Chargeable values(@Code, @Type, @ProfileCode, @Desc, @Inherit, @status, @SourceType) ";

            template.ExecuteNonQuery(CommandType.Text, strIns, CreateParameters(obj));

            // insert fields and members
            FieldAndMemberDAOBase.InsertFieldAndMember(obj.ChargeableCode, obj);
        }

        internal static void UpdateChargeable(Chargeable chargeable, bool isFullyUpdated)
        {
            string sqlUpdate = "UPDATE [dbo].[Chargeable] SET Chargeable_Type=@Type, "
                + "Profile_Code=@ProfileCode, Description=@Desc, Inherit_Profile_Member=@Inherit, "
                + "Status=@Status, Source_Type=@SourceType "
               + " WHERE Chargeable_Code = @Code";
            template.ExecuteNonQuery(CommandType.Text, sqlUpdate, CreateParameters(chargeable));

            if (!isFullyUpdated)
                return;

            // delete field and member
            string sqlDelete = "delete from Chargeable_Field where Chargeable_Code=@Code;"
                + "delete from Chargeable_Member where Chargeable_Code=@Code";
            template.ExecuteNonQuery(CommandType.Text, sqlDelete, CreateParameters(chargeable, false));

            // insert field and member
            FieldAndMemberDAOBase.InsertFieldAndMember(chargeable.ChargeableCode, chargeable);
        }

        internal static void UpdateField(Chargeable chargeable)
        {
            // delete field and member
            string sqlDelete = "delete from Chargeable_Field where Chargeable_Code=@Code;";
            template.ExecuteNonQuery(CommandType.Text, sqlDelete, CreateParameters(chargeable, false));

            // insert field and member
            FieldAndMemberDAOBase.InsertField(chargeable.ChargeableCode, chargeable);
        }

        internal static void UpdateMember(Chargeable chargeable)
        {
            // delete field and member
            string sqlDelete = "delete from Chargeable_Member where Chargeable_Code=@Code";
            template.ExecuteNonQuery(CommandType.Text, sqlDelete, CreateParameters(chargeable, false));

            // insert field and member
            FieldAndMemberDAOBase.InsertMember(chargeable.ChargeableCode, chargeable);
        }

        internal static IList<Chargeable> ListChargeable(string profileID)
        {
            return new List<Chargeable>();
        }

        #region private methods
        private static IDbParameters CreateParameters(Chargeable obj, bool isAll = true)
        {
            IDbParameters paras = template.CreateDbParameters();
            paras.Add("Code", DbType.String).Value = obj.ChargeableCode;

            if (!isAll)
                return paras;

            paras.Add("Type", DbType.String).Value = obj.ChargeableType.ToString();
            paras.Add("ProfileCode", DbType.String).Value = obj.ProfileCode;
            paras.Add("Desc", DbType.String).Value = obj.Description;
            paras.Add("Inherit", DbType.String).Value = KpmgObject.BoolToString(obj.IsMemberInherited);
            paras.Add("Status", DbType.String).Value = obj.Status.ToString();
            paras.Add("SourceType", DbType.String).Value = obj.Source.ToString();

            return paras;
        }

        private static Chargeable MappingRow(IDataReader reader, int rowNumber)
        {
            Chargeable chargeable = new Chargeable();

            chargeable.ChargeableCode = reader.GetString(0);
            chargeable.ChargeableType = (ChargeableType)Enum.Parse(typeof(ChargeableType), reader.GetString(1));
            chargeable.ProfileCode = reader.GetString(2);
            chargeable.Description = reader.GetString(3);
            chargeable.IsMemberInherited = reader.GetString(4) == "Y" ? true : false;
            chargeable.Status = (ChargeableStatus)Enum.Parse(typeof(ChargeableStatus), reader.GetString(5));
            chargeable.Source = (ChargeableSource)Enum.Parse(typeof(ChargeableSource), reader.GetString(6));

            return chargeable;
        }

        #endregion
    }
}
